package com.browsesoft.sqlserver;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Map;

import com.browsesoft.PropertiesService;
import com.browsesoft.dbtools.ConnectionPool;
import com.browsesoft.dbtools.DBField;
import com.browsesoft.dbtools.DBMetaDataCreateor;
import com.browsesoft.dbtools.DBMetaDataManager;
import com.browsesoft.dbtools.DBTable;
import com.browsesoft.dbtools.DBTools;
import com.browsesoft.dbtools.SQLStatement;

/**
 * Ms Sql Server数据库产生建表等SQL语句
 */
public class SQLServerStatement implements SQLStatement, DBMetaDataCreateor {
	/**
	 * 产生创建数据库表的语句。
	 * 
	 * @param tableName
	 *            表名
	 * @param fields
	 *            要建立的表的字段列表，包括字段名以及字段值
	 * @return 创建数据库表的SQL语句
	 */
	public String createTable(String tableName, Map fields) {
		// 准备sql语句
		String sql = "create table " + tableName + "(";
		Iterator enum1 = fields.keySet().iterator();
		// 对所有字段取字段
		while (enum1.hasNext()) {
			String fieldName = (String) enum1.next();
			String fieldValue = (String) fields.get(fieldName);
			// 预留200的空间
			int stringLength = (fieldValue.getBytes().length / 200 + 1) * 200;
			String stringType = ConnectionPool.getStringType();
			sql += fieldName + " " + stringType + "(" + stringLength + "),";
			// 主关键字字段sql语句准备
			if (fieldName.toLowerCase().equals("id")) {
				sql = sql.substring(0, sql.length() - 1);
				sql += " not null,";
			}
		}
		sql = sql.substring(0, sql.length() - 1) + ")";
		return sql;
	}

	/**
	 * 产生给数据库添加字段的语句。
	 * 
	 * @param tableName
	 *            表名
	 * @param fields
	 *            要增加的字段列表，包括字段名以及字段值
	 * @return 给数据库添加字段的语句
	 */
	public String addFields(String tableName, Map fields) {
		try {
			// 准备sql语句
			String owner = PropertiesService.getProperty("Database", "user");
			String sql = "ALTER TABLE " + tableName + " ADD ";
			Iterator enum1 = fields.keySet().iterator();
			// 对所有字段取字段
			int i = 1;
			while (enum1.hasNext()) {
				String fieldName = (String) enum1.next();
				String fieldValue = (String) fields.get(fieldName);
				// 预留200的空间
				int stringLength = (fieldValue.length() / 200 + 1) * 200;
				// 如果不是第一列则用逗号分隔
				if (i != 1) {
					sql = sql + ",";
				}
				String stringType = ConnectionPool.getStringType();
				sql = sql + fieldName + " " + stringType + "(" + stringLength
						+ ")";
				i++;
			}
			return sql;
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		}
	}

	/**
	 * 产生扩展数据库字段长度的语句。
	 * 
	 * @param tableName
	 *            表名
	 * @param fields
	 *            要扩展的字段列表，包括字段名以及字段值
	 * @return 扩展数据库字段长度的语句
	 */
	public String extendFieldsLength(String tableName, Map fields) {
		// 准备sql语句
		String sql = "ALTER TABLE " + tableName + " alter column ";
		Iterator enum1 = fields.keySet().iterator();
		// 对所有字段取字段信息
		int i = 1;
		while (enum1.hasNext()) {
			String fieldName = (String) enum1.next();
			String fieldValue = (String) fields.get(fieldName);
			// 预留200的空间
			int stringLength = (fieldValue.length() / 200 + 1) * 200;
			// 如果不是第一列则用逗号分隔
			if (i != 1) {
				sql = sql + ",";
			}
			String stringType = ConnectionPool.getStringType();
			sql = sql + fieldName + " " + stringType + "(" + stringLength + ")";
			i++;
		}
		return sql;
	}

	/**
	 * 把给定字符串按数据库的大小写规则进行转换
	 */
	public String translateString(String value) {
		return value;
	}

	/**
	 * 建表结构
	 * 
	 */
	public void createTableStruct(String tableName, Hashtable newFields)
			throws Exception {
		try {
			// 如果表不存在则建表结构
			DBTable table = DBMetaDataManager.getInstance().getDBTable(
					tableName);
			if (table == null) {
				DBTools.createTable(tableName, newFields);
				table = DBMetaDataManager.getInstance().getDBTable(tableName);
			}
			// 否则对原表进行维护
			else {
				Hashtable addFieldsHt = new Hashtable();
				Hashtable modifyFieldsHt = new Hashtable();
				Enumeration newEnum = newFields.keys();
				// 逐个取新表字段与旧表字段对比
				while (newEnum.hasMoreElements()) {
					String newField = (String) newEnum.nextElement();
					newField = newField.toLowerCase();
					String newFieldValue = newFields.get(newField).toString();
					// 如果字段名包含在原表中
					DBField field = table.getField(newField);
					// 如果字段名包含在原表中
					if (field != null) {
						if (newFieldValue.getBytes().length > field.getLength()) {
							modifyFieldsHt.put(newField, newFieldValue);
						}
					}
					// 否则字段名不包含在原表中
					else {
						{
							// 准备要增加的字段哈希表
							addFieldsHt.put(newField, newFieldValue);
						}
					}
				}
				// 增如字段
				if (addFieldsHt.size() > 0) {
					// 如果修改表结构不成功，则忽略
					DBTools.addFields(tableName, addFieldsHt);
					table.addFields(this.getFields(addFieldsHt));
				}
				// 扩充字段长度
				if (modifyFieldsHt.size() > 0) {
					// 如果修改表结构不成功，则忽略
					DBTools.extendFieldsLength(tableName, modifyFieldsHt);
					table.addFields(this.getFields(addFieldsHt));
				}
			}
		} catch (Exception ex) {
		}
	}

	/**
	 * 根据表名得到表中字段的数据类型
	 */
	public Hashtable getDataTypes(String tableName) throws Exception {
		Hashtable ht = new Hashtable();
		Connection connection = ConnectionPool.getDBConnection();
		DatabaseMetaData dmd = connection.getMetaData();
		// 得到列信息
		ResultSet rs = dmd.getColumns(null, "%", tableName, "%");
		// System.out.println("tablename:"+tableName);
		// 得到字段名和字段类型
		while (rs.next()) {
			String fieldName = rs.getString("COLUMN_NAME");
			String fieldType = rs.getString("TYPE_NAME");
			ht.put(fieldName, fieldType);
			// System.out.println(fieldName+":"+fieldType);
		}
		ConnectionPool.putDBConnection(connection);
		return ht;
	}

	/**
	 * 根据表结构得到字段信息包括（字段名，字段长度）
	 */
	public Hashtable getFieldLength(String[][] data) throws Exception {
		// 返回值为 ht
		Hashtable ht = new Hashtable();
		// 得到字段结构
		// 准备列号
		String[] head = new String[data[0].length];
		for (int i = 0; i < data[0].length; i++) {
			head[i] = data[0][i];
		}
		int fieldname_column = DBTools.indexOf(head, "COLUMN_NAME");
		int fieldSize_column = DBTools.indexOf(head, "COLUMN_SIZE");
		for (int i = 1; i < data.length; i++) {
			String fieldname = data[i][fieldname_column].toLowerCase();
			String fieldSize = data[i][fieldSize_column];
			ht.put(fieldname, fieldSize);
			// System.out.println("列明"+fieldname+"长度"+fieldSize);
		}
		return ht;
	}

	/**
	 * 得到数据库中所有表名
	 */
	public String[] getTableNames() throws Exception {
		Connection connection = null;
		ResultSet rs = null;
		try {
			connection = ConnectionPool.getDBConnection();
			// 取出所有的表名
			DatabaseMetaData dmd = connection.getMetaData();
			String name = "%";
			String[] types = { "TABLE" };
			rs = dmd.getTables(null, "%", translateString(name), types);
			String[][] ret = DBTools.parseResultWithTableHead(rs);
			String[] tableNames = new String[ret.length - 1];
			for (int i = 1; i < ret.length; i++) {
				tableNames[i - 1] = ret[i][2];
			}
			return tableNames;
		} finally {
			rs.close();
			ConnectionPool.putDBConnection(connection);
		}
	}

	/**
	 * 判断表是否存在
	 */
	public boolean isTableExist(String tableName) throws Exception {
		Connection connection = null;
		ResultSet rs = null;
		try {
			connection = ConnectionPool.getDBConnection();
			// 取出所有的表名
			DatabaseMetaData dmd = connection.getMetaData();
			String[] types = { "TABLE" };
			rs = dmd.getTables(null, "%", translateString(tableName), types);
			// 如果该表名存在则返回值为真
			return rs.next();
		} finally {
			rs.close();
			ConnectionPool.putDBConnection(connection);
		}
	}

	/**
	 * 把hashtable转换成字段列表
	 */
	private Map getFields(Hashtable ht) {
		Map result = new HashMap();
		// 对于每一个字段，产生新字段信息，放到表信息中
		Iterator iter = ht.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry entry = (Map.Entry) iter.next();
			String name = (String) entry.getKey();
			String len = (String) entry.getValue();
			DBField field = new DBField(name, Integer.parseInt(len));
			result.put(name, field);
		}
		return result;
	}

	/**
	 * 得到表信息
	 */
	public DBTable getDBTable(String tableName) {
		Connection connection = null;
		ResultSet rs = null;
		try {
			// 如果表不存在，返回空
			if (!isTableExist(tableName)) {
				return null;
			}
			DBTable result = new DBTable(tableName);
			// 取原表数据
			connection = ConnectionPool.getDBConnection();
			DatabaseMetaData dmd = connection.getMetaData();
			rs = dmd.getColumns(null, "%", translateString(tableName), "%");
			String[][] data = DBTools.parseResultWithTableHead(rs);
			Map fields = this.getFields(getFieldLength(data));
			System.out.println("===表明" + tableName);
			System.out.println(fields);
			result.setFields(fields);
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if (connection != null) {
				try {
					ConnectionPool.putDBConnection(connection);
				} catch (SQLException e) {
				}
			}
		}
	}

	public String DBKeyDispose(String fieldName) {
		return fieldName;
	}

	public String disposeLimit(String sql, int start, int number) {
		String result="select * from (select top 1 * from t_userfiles)t1,( select top 2 * from t_userfiles)t2 where t1.id<>t2.id";
//		if(start==0)
//		{
//			result="select TOP "+number+" * FROM t_userfiles";
//		}else{
//			result = "SELECT * FROM ( SELECT TOP "+1+" * FROM (SELECT TOP "+2+" * FROM t_userfiles order by id desc) t1) t2 Order by id asc";
//		}
		
		return sql;
	}

}
